<?php
namespace app\admin\model;

use think\Model;
use think\facade\Config;
use \think\Db;

class Searchcount extends Model
{  
   
    protected $connection = 'publicdata';
	public function get_preview_click($page,$limit,$starts,$end){
		$default_lang=config('app.default_lang');
        
		$start=($page-1)*$limit+1;
		$BeginDate=date('Y-m-01 00:00:00', strtotime(date("Y-m-d")));
		if($default_lang=='en-us'){
			if($starts=='0'&&$end=='0'){
				$list=Searchcount::field('leftclick,count(id) as b')->whereNotNull('leftclick')->where([['create_time','>=',$BeginDate],['country','<>','中国']])->group('leftclick')->order('b', 'desc')->limit(($start-1),$limit)->select()->toArray();
				$count=Searchcount::field('count(id) as b')->whereNotNull('leftclick')->where([['create_time','>=',$BeginDate],['country','<>','中国']])->group('leftclick')->select()->toArray();
			}else{
				$list=Searchcount::field('leftclick,count(id) as b')->whereNotNull('leftclick')->where([['create_time','>=',$starts],['create_time','<=',$end],['country','<>','中国']])->group('leftclick')->order('b', 'desc')->limit(($start-1),$limit)->select()->toArray();
				$count=Searchcount::field('count(id) as b')->whereNotNull('leftclick')->where([['create_time','>=',$starts],['create_time','<=',$end],['country','<>','中国']])->group('leftclick')->select()->toArray();
			}
		}else{
			if($starts=='0'&&$end=='0'){
				$list=Searchcount::field('leftclick,count(id) as b')->whereNotNull('leftclick')->where('create_time','>=',$BeginDate)->group('leftclick')->order('b', 'desc')->limit(($start-1),$limit)->select()->toArray();
				$count=Searchcount::field('count(id) as b')->whereNotNull('leftclick')->where('create_time','>=',$BeginDate)->group('leftclick')->select()->toArray();
			}else{
				$list=Searchcount::field('leftclick,count(id) as b')->whereNotNull('leftclick')->where([['create_time','>=',$starts],['create_time','<=',$end]])->group('leftclick')->order('b', 'desc')->limit(($start-1),$limit)->select()->toArray();
				$count=Searchcount::field('count(id) as b')->whereNotNull('leftclick')->where([['create_time','>=',$starts],['create_time','<=',$end]])->group('leftclick')->select()->toArray();
			}
		}
		$data['count']=count($count);
        $data['list']=$list;
        return $data;
	}

	public function get_result_click($page,$limit,$starts,$end){
		$default_lang=config('app.default_lang');
		$start=($page-1)*$limit+1;
		$BeginDate=date('Y-m-01 00:00:00', strtotime(date("Y-m-d")));
		if($default_lang=='en-us'){
			if($starts=='0'&&$end=='0'){
				$list=Searchcount::field('rightclick,count(id) as b')->whereNotNull('rightclick')->where([['create_time','>=',$BeginDate],['country','<>','中国']])->group('rightclick')->order('b', 'desc')->limit(($start-1),$limit)->select()->toArray();
				$count=Searchcount::field('count(id) as b')->whereNotNull('rightclick')->where([['create_time','>=',$BeginDate],['country','<>','中国']])->group('rightclick')->select()->toArray();
			}else{
				$list=Searchcount::field('rightclick,count(id) as b')->whereNotNull('rightclick')->where([['create_time','>=',$starts],['create_time','<=',$end],['country','<>','中国']])->group('rightclick')->order('b', 'desc')->limit(($start-1),$limit)->select()->toArray();
				$count=Searchcount::field('count(id) as b')->whereNotNull('rightclick')->where([['create_time','>=',$starts],['create_time','<=',$end],['country','<>','中国']])->group('rightclick')->select()->toArray();
			}
		}else{
			if($starts=='0'&&$end=='0'){
				$list=Searchcount::field('rightclick,count(id) as b')->whereNotNull('rightclick')->where('create_time','>=',$BeginDate)->group('rightclick')->order('b', 'desc')->limit(($start-1),$limit)->select()->toArray();
				$count=Searchcount::field('count(id) as b')->whereNotNull('rightclick')->where('create_time','>=',$BeginDate)->group('rightclick')->select()->toArray();
			}else{
				$list=Searchcount::field('rightclick,count(id) as b')->whereNotNull('rightclick')->where([['create_time','>=',$starts],['create_time','<=',$end]])->group('rightclick')->order('b', 'desc')->limit(($start-1),$limit)->select()->toArray();
				$count=Searchcount::field('count(id) as b')->whereNotNull('rightclick')->where([['create_time','>=',$starts],['create_time','<=',$end]])->group('rightclick')->select()->toArray();
			}
		}
		$data['count']=count($count);
        $data['list']=$list;
        return $data;
	}


	public function get_all($page,$limit,$starts,$end){
		$default_lang=config('app.default_lang');
		$start=($page-1)*$limit+1;
		$BeginDate=date('Y-m-01 00:00:00', strtotime(date("Y-m-d")));
        if($default_lang=='en-us'){
			if($starts=='0'&&$end=='0'){
				$list=Searchcount::field('sou,count(id) as b')->whereNotNull('sou')->where([['create_time','>=',$BeginDate],['country','<>','中国']])->group('sou')->order('b', 'desc')->limit(($start-1),$limit)->select()->toArray();
				
				$count=Searchcount::field('count(id) as b')->whereNotNull('sou')->where([['create_time','>=',$BeginDate],['country','<>','中国']])->group('sou')->select()->toArray();
			}else{
				$list=Searchcount::field('sou,count(id) as b')->whereNotNull('sou')->where([['create_time','>=',$starts],['create_time','<=',$end],['country','<>','中国']])->group('sou')->order('b', 'desc')->limit(($start-1),$limit)->select()->toArray();
				$count=Searchcount::field('count(id) as b')->whereNotNull('sou')->where([['create_time','>=',$starts],['create_time','<=',$end],['country','<>','中国']])->group('sou')->select()->toArray();
			}
		}else{
			if($starts=='0'&&$end=='0'){
				$list=Searchcount::field('sou,count(id) as b')->whereNotNull('sou')->where('create_time','>=',$BeginDate)->group('sou')->order('b', 'desc')->limit(($start-1),$limit)->select()->toArray();
				
				$count=Searchcount::field('count(id) as b')->whereNotNull('sou')->where('create_time','>=',$BeginDate)->group('sou')->select()->toArray();
			}else{
				$list=Searchcount::field('sou,count(id) as b')->whereNotNull('sou')->where([['create_time','>=',$starts],['create_time','<=',$end]])->group('sou')->order('b', 'desc')->limit(($start-1),$limit)->select()->toArray();
				$count=Searchcount::field('count(id) as b')->whereNotNull('sou')->where([['create_time','>=',$starts],['create_time','<=',$end]])->group('sou')->select()->toArray();
			}
		}
		$data['count']=count($count);
        $data['list']=$list;
        return $data;
	}

	public function get_country($page,$limit,$starts_time,$end_time,$fields){
		$default_lang=config('app.default_lang');
        $start=($page-1)*$limit+1;
		if($default_lang=='en-us'){
			$list=Searchcount::field('country,count(id) as b')->whereNotNull($fields)->where([['create_time','>=',$starts_time],['create_time','<=',$end_time],['country','<>','中国']])->group('country')->order(['b'=>'desc','id'=>'desc'])->limit(($start-1),$limit)->select()->toArray();
			$count=Searchcount::field('count(id) as b')->whereNotNull($fields)->where([['create_time','>=',$starts_time],['create_time','<=',$end_time],['country','<>','中国']])->group('country')->select()->toArray();
			foreach ($list as $key => $value) {
				$list[$key]['keywords']=Searchcount::field($fields.',count(id) as b')->whereNotNull($fields)->where([['create_time','>=',$starts_time],['create_time','<=',$end_time],['country','<>','中国'],['country','=',$value['country']]])->group($fields)->order(['b'=>'desc','id'=>'desc'])->limit(0,10)->select()->toArray();
				$list[$key]['starts_time']=$starts_time;
				$list[$key]['end_time']=$end_time;
			}
		}else{
			$list=Searchcount::field('country,count(id) as b')->whereNotNull($fields)->where([['create_time','>=',$starts_time],['create_time','<=',$end_time]])->group('country')->order(['b'=>'desc','id'=>'desc'])->limit(($start-1),$limit)->select()->toArray();
			$count=Searchcount::field('count(id) as b')->whereNotNull($fields)->where([['create_time','>=',$starts_time],['create_time','<=',$end_time]])->group('country')->select()->toArray();
			foreach ($list as $key => $value) {
				$list[$key]['keywords']=Searchcount::field($fields.',count(id) as b')->whereNotNull($fields)->where([['create_time','>=',$starts_time],['create_time','<=',$end_time],['country','=',$value['country']]])->group($fields)->order(['b'=>'desc','id'=>'desc'])->limit(0,10)->select()->toArray();
				$list[$key]['starts_time']=$starts_time;
				$list[$key]['end_time']=$end_time;
			}
		}

		$data['count']=count($count);
        $data['list']=$list;
        return $data;
	}

	public function get_country_xsl($country,$starts_time,$end_time,$fields){
		$default_lang=config('app.default_lang');
		if($default_lang=='en-us'){
	        $list=Searchcount::field($fields.',count(id) as b')->whereNotNull($fields)->where([['create_time','>=',$starts_time],['create_time','<=',$end_time],['country','=',$country],['country','<>','中国']])->group($fields)->order(['b'=>'desc','id'=>'desc'])->select()->toArray();
	        foreach ($list as $key => $value) {
	        	$list[$key]['detail']=Searchcount::field('ip,sys,create_time')->where([['create_time','>=',$starts_time],['create_time','<=',$end_time],['country','<>','中国'],['country','=',$country],[$fields,'=',$value[$fields]]])->order(['id'=>'desc'])->select()->toArray();
	        }
	    }else{
	    	$list=Searchcount::field($fields.',count(id) as b')->whereNotNull($fields)->where([['create_time','>=',$starts_time],['create_time','<=',$end_time],['country','=',$country]])->group($fields)->order(['b'=>'desc','id'=>'desc'])->select()->toArray();
	        foreach ($list as $key => $value) {
	        	$list[$key]['detail']=Searchcount::field('ip,sys,create_time')->where([['create_time','>=',$starts_time],['create_time','<=',$end_time],['country','=',$country],[$fields,'=',$value[$fields]]])->order(['id'=>'desc'])->select()->toArray();
	        }
	    }
        return $list;
	}



	public function get_all_null($page,$limit,$starts,$end){
		$default_lang=config('app.default_lang');
		$start=($page-1)*$limit+1;
		$BeginDate=date('Y-m-01 00:00:00', strtotime(date("Y-m-d")));
		if($default_lang=='en-us'){
			if($starts=='0'&&$end=='0'){
	            $list=Searchcount::field('noresult,count(id) as b')->whereNotNull('noresult')->where([['create_time','>=',$BeginDate],['country','<>','中国']])->group('noresult')->order('b', 'desc')->limit(($start-1),$limit)->select()->toArray();
			    $count=Searchcount::field('count(id) as b')->whereNotNull('noresult')->where([['create_time','>=',$BeginDate],['country','<>','中国']])->group('noresult')->select()->toArray();
			}else{
	            $list=Searchcount::field('noresult,count(id) as b')->whereNotNull('noresult')->where([['create_time','>=',$starts],['create_time','<=',$end],['country','<>','中国']])->group('noresult')->order('b', 'desc')->limit(($start-1),$limit)->select()->toArray();
			    $count=Searchcount::field('count(id) as b')->whereNotNull('noresult')->where([['create_time','>=',$starts],['create_time','<=',$end],['country','<>','中国']])->group('noresult')->select()->toArray();
			}
		}else{
			if($starts=='0'&&$end=='0'){
	            $list=Searchcount::field('noresult,count(id) as b')->whereNotNull('noresult')->where([['create_time','>=',$BeginDate]])->group('noresult')->order('b', 'desc')->limit(($start-1),$limit)->select()->toArray();
			    $count=Searchcount::field('count(id) as b')->whereNotNull('noresult')->where('create_time','>=',$BeginDate)->group('noresult')->select()->toArray();
			}else{
	            $list=Searchcount::field('noresult,count(id) as b')->whereNotNull('noresult')->where([['create_time','>=',$starts],['create_time','<=',$end]])->group('noresult')->order('b', 'desc')->limit(($start-1),$limit)->select()->toArray();
			    $count=Searchcount::field('count(id) as b')->whereNotNull('noresult')->where([['create_time','>=',$starts],['create_time','<=',$end]])->group('noresult')->select()->toArray();
			}
		}
		$data['count']=count($count);
        $data['list']=$list;
        return $data;
	}

	public function daochu($start,$end,$t){
		$default_lang=config('app.default_lang');
		if($default_lang=='en-us'){
			if(!empty($end)){
	            if($t=='1'){
					$data=Searchcount::field('sou,count(id) as b')->whereNotNull('sou')->where([['create_time','>=',$start],['create_time','<=',$end],['country','<>','中国']])->group('sou')->order('b', 'desc')->select()->toArray();
				}
				if($t=='2'){
					$data=Searchcount::field('noresult,count(id) as b')->whereNotNull('noresult')->where([['create_time','>=',$start],['create_time','<=',$end],['country','<>','中国']])->group('noresult')->order('b', 'desc')->select()->toArray();
				}
				if($t=='3'){
					$data=Searchcount::field('leftclick,count(id) as b')->whereNotNull('leftclick')->where([['create_time','>=',$start],['create_time','<=',$end],['country','<>','中国']])->group('leftclick')->order('b', 'desc')->select()->toArray();
				}
				if($t=='4'){
					$data=Searchcount::field('rightclick,count(id) as b')->whereNotNull('rightclick')->where([['create_time','>=',$start],['create_time','<=',$end],['country','<>','中国']])->group('rightclick')->order('b', 'desc')->select()->toArray();
				}
				
			}else{
				if($t=='1'){
					$data=Searchcount::field('sou,count(id) as b')->whereNotNull('sou')->where([['create_time','>=',$start],['country','<>','中国']])->group('sou')->order('b', 'desc')->select()->toArray();
				}
				if($t=='2'){
					$data=Searchcount::field('noresult,count(id) as b')->whereNotNull('noresult')->where([['create_time','>=',$start],['country','<>','中国']])->group('noresult')->order('b', 'desc')->select()->toArray();
				}
				if($t=='3'){
					$data=Searchcount::field('leftclick,count(id) as b')->whereNotNull('leftclick')->where([['create_time','>=',$start],['country','<>','中国']])->group('leftclick')->order('b', 'desc')->select()->toArray();
				}
				if($t=='4'){
					$data=Searchcount::field('rightclick,count(id) as b')->whereNotNull('rightclick')->where([['create_time','>=',$start],['country','<>','中国']])->group('rightclick')->order('b', 'desc')->select()->toArray();
				}
				
			}
		}else{
			if(!empty($end)){
	            if($t=='1'){
					$data=Searchcount::field('sou,count(id) as b')->whereNotNull('sou')->where([['create_time','>=',$start],['create_time','<=',$end]])->group('sou')->order('b', 'desc')->select()->toArray();
				}
				if($t=='2'){
					$data=Searchcount::field('noresult,count(id) as b')->whereNotNull('noresult')->where([['create_time','>=',$start],['create_time','<=',$end]])->group('noresult')->order('b', 'desc')->select()->toArray();
				}
				if($t=='3'){
					$data=Searchcount::field('leftclick,count(id) as b')->whereNotNull('leftclick')->where([['create_time','>=',$start],['create_time','<=',$end]])->group('leftclick')->order('b', 'desc')->select()->toArray();
				}
				if($t=='4'){
					$data=Searchcount::field('rightclick,count(id) as b')->whereNotNull('rightclick')->where([['create_time','>=',$start],['create_time','<=',$end]])->group('rightclick')->order('b', 'desc')->select()->toArray();
				}
				
			}else{
				if($t=='1'){
					$data=Searchcount::field('sou,count(id) as b')->whereNotNull('sou')->where('create_time','>=',$start)->group('sou')->order('b', 'desc')->select()->toArray();
				}
				if($t=='2'){
					$data=Searchcount::field('noresult,count(id) as b')->whereNotNull('noresult')->where('create_time','>=',$start)->group('noresult')->order('b', 'desc')->select()->toArray();
				}
				if($t=='3'){
					$data=Searchcount::field('leftclick,count(id) as b')->whereNotNull('leftclick')->where('create_time','>=',$start)->group('leftclick')->order('b', 'desc')->select()->toArray();
				}
				if($t=='4'){
					$data=Searchcount::field('rightclick,count(id) as b')->whereNotNull('rightclick')->where('create_time','>=',$start)->group('rightclick')->order('b', 'desc')->select()->toArray();
				}
				
			}
		}
		
		
		return $data;

	}

	public function all_search(){
		$data=Searchcount::all();
		return $data;

	}

}